package com.py.credit.controller;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.PushbackInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.py.credit.common.ServiceException;
import com.py.credit.common.logs.ExceptionLogger;
import com.py.credit.common.rest.RestServiceResult;
import com.py.credit.entity.CallAchievementsLog;
import com.py.credit.entity.CallInterfaceInfo;
import com.py.credit.entity.ReturnResult;
import com.py.credit.entity.ReturnResultJson;
import com.py.credit.entity.ReturnResultObj;
import com.py.credit.entity.SysUser;
import com.py.credit.entity.common.OptionalColumn;
import com.py.credit.entity.enums.CreditServerEnum;
import com.py.credit.service.CreditServerService;
import com.py.credit.service.XypjRemoteBaseService;
import com.py.credit.utils.DateUtil;
import com.py.credit.utils.ExcelUtil;
import com.py.credit.utils.FileUtils;
import com.py.credit.utils.MapCache;
import com.py.credit.utils.UuidUtil;
import com.py.framework.core.common.Page;
import com.py.framework.core.entity.SysOfficeEntity;
import com.py.framework.core.helper.HttpServletHelper;

@RestController
@RequestMapping(value = "/creditServer")
public class CreditServerController {
	private final static String FRONT_TABLE_ID = "creditServer_";

	// 请求信息必填字段
	private static final List<String> mustParams = new ArrayList<String>();

	// 反馈必填
	private static final List<String> feedbackMustParams = new ArrayList<String>();

	// 模板首行字段
	private static final Map<Integer, String> excelTemplete = new HashMap<>();

	// 业务类型
	private static final List<Integer> businessList = new ArrayList<>();

	// 主体类型
	private static final List<String> ztTypeList = new ArrayList<>();
	
	private static final List<String> timeList = new ArrayList<>();

	static {
		mustParams.add("servicecode");// 业务类型
		mustParams.add("dept"); // 部门编码
		mustParams.add("deptname"); // 部门名称
		mustParams.add("name"); // 主体 张三12345679812345679
		mustParams.add("ztType"); // 主体类型 100000 法人 100001 自然人

		feedbackMustParams.add("checkcode"); // 取参时候的唯一key，对应一条记录
		feedbackMustParams.add("usage"); // 用途

		excelTemplete.put(0, "调用部门");
		// excelTemplete.put(1, "业务类型");
		excelTemplete.put(1, "主体类型");
		excelTemplete.put(2, "主体名称");
		excelTemplete.put(3, "主体代码");
		excelTemplete.put(4, "信用等级");
		excelTemplete.put(5, "信用分");
		excelTemplete.put(6, "用途内容");
		excelTemplete.put(7, "反馈时间");

		businessList.add(2);
		businessList.add(3);
		businessList.add(4);
		businessList.add(5);
		businessList.add(6);

		ztTypeList.add("100000");
		ztTypeList.add("100001");
		ztTypeList.add("100002");
		
		timeList.add("yyyyMMdd");
		timeList.add("yyyyMMddHHmm");
		timeList.add("yyyyMMddHHmmss");
		timeList.add("yyyy年MM月dd日");
		timeList.add("yyyy年MM月dd日HH时mm分");
		timeList.add("yyyy年MM月dd日HH时mm分ss秒");
		timeList.add("yyyy年MM月dd");
		timeList.add("yyyy年MM月dd HH时mm分");
		timeList.add("yyyy年MM月dd HH时mm分ss秒");
		timeList.add("yyyy年MM月dd");
		timeList.add("yyyy年MM月dd HH时mm分");
		timeList.add("yyyy年MM月dd HH时mm分ss秒");
		timeList.add("yyyy-MM-dd");
		timeList.add("yyyy-MM-dd HH-mm");
		timeList.add("yyyy-MM-dd HH-mm-ss");
		timeList.add("yyyy/MM/dd");
		timeList.add("yyyy/MM/dd HH/mm");
		timeList.add("yyyy/MM/dd HH/mm/ss");
		timeList.add("yyyy.MM.dd");
		timeList.add("yyyy.MM.dd HH.mm");
		timeList.add("yyyy.MM.dd HH.mm.ss");
		timeList.add("yyyy-MM-dd");
		timeList.add("yyyy-MM-dd HH:mm");
		timeList.add("yyyy-MM-dd HH:mm:ss");
	}

	@Autowired
	private XypjRemoteBaseService xypjRemoteBaseService;

	@Autowired
	private CreditServerService creditServerService;

	@Value("${absolutepath}")
	private String absolutepath;

	/**
	 * 根据用户传的json串查信用分
	 * 
	 * @param message
	 * @param page
	 * @param request
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Transactional(rollbackFor = Exception.class)
	@RequestMapping(value = "/getZtScore.json", method = RequestMethod.POST)
	public RestServiceResult<ReturnResult> getZtScore(
			@RequestParam(value = "message", required = true, defaultValue = "") String message) {

		// 校验请求json是否存在
		if (StringUtils.isEmpty(message)) {
			throw new ServiceException("请输入必要的查询条件！");
		}

		// 校验请求json格式是否正确
		Map<String, String> requestMap = new HashMap<>();
		try {
			requestMap = (Map<String, String>) JSON.parse(message);
		} catch (Exception e) {
			throw new ServiceException("请按照正确的格式输入参数！");
		}

		// 校验参数
		String ztDm = ""; // 主体代码
		String ztName = ""; // 主体名称
		Set<String> keySet = requestMap.keySet();
		for (String one : mustParams) {
			if (!keySet.contains(one)) {
				// 校验请求json是否包含必要的参数
				throw new ServiceException("缺少必要参数" + one + "!");
			}
			// 校验参数值是否为空
			if (StringUtils.isEmpty(requestMap.get(one))) {
				throw new ServiceException(one + "的值不能为空！");
			} else {
				if ("name".equals(one)) {
					
					if(one.length() < 20) {
						throw new ServiceException("主体名称代码输入不正确！");
					}
					
					String newOne = one.substring(one.length() - 18);
					
					Pattern patternDm = Pattern.compile("[A-Za-z0-9]");
					
					for(int n = 0 ; n < newOne.length() ; n++) {
						Matcher matcherDm = patternDm.matcher(newOne.charAt(n) + "");
						if(!matcherDm.find()) {
							throw new ServiceException("主体代码输入不正确！");
						}
					}
					
					ztDm = newOne;
					
					String zt = requestMap.get("name");

					ztName = zt.substring(0, zt.length() - 18);
				}
			}
		}

		// 获取校验业务类型
		String serviceCode = requestMap.get("servicecode");
		try {
			int businessType = Integer.parseInt(serviceCode);
			if (businessList.contains(businessType)) {
			} else {
				throw new ServiceException("业务类型代号不在支持的业务范围内！");
			}
		} catch (Exception e) {
			throw new ServiceException("业务类型请输入代号（2，3，4，5，6）！");
		}

		// 获取校验主体类型
		String ztType = requestMap.get("ztType");
		try {
			Integer.parseInt(ztType);
			if (ztTypeList.contains(ztType)) {
			} else {
				throw new ServiceException("主体类型代号不在支持的范围内！");
			}
		} catch (Exception e) {
			throw new ServiceException("主体类型代号不在范围：100000（法人），100001（自然人），100002（非企业法人）！");
		}

		// 校验部门
		Long deptId = 0l;
		String deptName = requestMap.get("deptname");
		try {
			deptId = Long.parseLong(requestMap.get("dept"));
		} catch (Exception e) {
			throw new ServiceException("部门id输入不正确！");
		}

		RestServiceResult<SysOfficeEntity> dept = xypjRemoteBaseService.getOfficesById(deptId);
		if (null == dept || null == dept.getData()) {
			throw new ServiceException("您需要查询的部门并不存在，请核实后再试！");
		}
		if (!deptName.equals(dept.getData().getOfficeName())) {
			throw new ServiceException("您输入的部门名称与部门编号不一致，请核实后再试！！");
		}

		CallInterfaceInfo callInfo = new CallInterfaceInfo();
		callInfo.setServicecode(serviceCode);
		callInfo.setDeptId(deptId);
		callInfo.setDeptName(deptName);
		callInfo.setZtType(ztType);
		callInfo.setZtName(ztName);
		callInfo.setZtDm(ztDm);

		RestServiceResult<ReturnResult> result = new RestServiceResult<ReturnResult>();

		// 根据所选业务id(服务编码)和某部门id和主体类型查询部门所分配的模型
		try {
			Map<String, Object> modelMap = creditServerService.getModelByOffice(serviceCode, deptId, ztType);

			ReturnResultJson returnResultJson = null;
			if (modelMap != null && modelMap.get("model_id") != null) {
				returnResultJson = creditServerService.getMap(String.valueOf(modelMap.get("model_id")), ztName, ztDm);
			} else {
				returnResultJson = creditServerService.getMapOth(ztName, ztDm);
			}

			// 封装返回参数
			if (null != returnResultJson) {
				ReturnResult returnResult = new ReturnResult();
				returnResult.setCheckcode(returnResultJson.getCheckcode());
				returnResult.setCreditlevel(returnResultJson.getCreditlevel());

				callInfo.setCheckcode(returnResultJson.getCheckcode());
				callInfo.setCreditlevel(returnResultJson.getCreditlevel());

				ReturnResultObj returnResultObj = new ReturnResultObj();
				returnResultObj.setName(returnResultJson.getName());
				returnResultObj.setScore(returnResultJson.getScore());
				returnResultObj.setUscc(returnResultJson.getUscc());

				callInfo.setReturnName(returnResultJson.getName());
				callInfo.setReturnUscc(returnResultJson.getUscc());
				callInfo.setReturnScore(returnResultJson.getScore());

				returnResult.setResultmsg(returnResultObj);

				UuidUtil.getInstance();
				String checkcode = UuidUtil.getUUid();
				returnResult.setCheckcode(checkcode);

				callInfo.setCheckcode(checkcode);

				// 插入记录
				creditServerService.insertCallInfo(callInfo);

				result.setData(returnResult);
				result.setCode(RestServiceResult.CODE_SUCCESS);
				return result;
			}
		} catch (Exception e) {

			result.setCode(RestServiceResult.CODE_UNKNOW);
			result.setMsg("系统异常，请联系管理员!");
			return result;
		}

		// 插入記錄
		creditServerService.insertCallInfo(callInfo);
		result.setCode(RestServiceResult.CODE_SUCCESS);
		result.setMsg("暂无此主体信用信息记录！");
		return result;
	}

	/**
	 * 反馈
	 * 
	 * @param message
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping(value = "/feedback.json", method = RequestMethod.POST)
	public RestServiceResult<Object> feedback(
			@RequestParam(value = "message", required = true, defaultValue = "") String message) {

		// 校验请求json是否存在
		if (StringUtils.isEmpty(message)) {
			throw new ServiceException("请输入必要的反馈条件！");
		}

		// 校验请求json格式是否正确
		Map<String, String> requestMap = new HashMap<>();
		try {
			requestMap = (Map<String, String>) JSON.parse(message);
		} catch (Exception e) {
			throw new ServiceException("请按照正确的格式输入参数！");
		}

		// 校验参数
		Set<String> keySet = requestMap.keySet();
		for (String one : feedbackMustParams) {
			if (!keySet.contains(one)) {
				// 校验请求json是否包含必要的参数
				throw new ServiceException("缺少必要参数" + one + "!");
			}
			// 校验参数值是否为空
			if (StringUtils.isEmpty(requestMap.get(one))) {
				throw new ServiceException(one + "的值不能为空！");
			}
		}

		String checkcode = requestMap.get("checkcode");
		String usage = requestMap.get("usage");

		RestServiceResult<Object> result = new RestServiceResult<Object>();

		int successCount = 0;
		try {
			successCount = creditServerService.insertFeedbackInfo(usage, checkcode);
		} catch (Exception e) {
			result.setCode(RestServiceResult.CODE_UNKNOW);
			result.setMsg("系统异常！请联系管理员！");
			return result;
		}

		if (successCount > 0) {
			result.setCode(RestServiceResult.CODE_SUCCESS);
			result.setMsg("反馈成功！感谢您的配合！");
		} else {
			result.setCode(RestServiceResult.CODE_SUCCESS);
			result.setMsg("反馈失败！请联系管理员！");
		}

		return result;
	}

	/**
	 * 获取成果列表
	 * 
	 * @param deptId
	 * @param rank
	 * @param ztType
	 * @param ywId
	 * @param condition
	 * @param page
	 * @param request
	 * @return
	 */
	@RequestMapping(value = "/getAchievements.json")
	public RestServiceResult<List<Map<String, Object>>> getAchievements(
			@RequestParam(value = "deptId", defaultValue = "", required = false) Long deptId,
			@RequestParam(value = "ywId", defaultValue = "", required = false) String ywId,
			@RequestParam(value = "ztType", defaultValue = "", required = false) String ztType,
			@RequestParam(value = "condition", defaultValue = "", required = false) String condition, Page page,
			HttpServletRequest request) {

		String token = HttpServletHelper.getUserToken(request);
		RestServiceResult<List<Map<String, Object>>> result = new RestServiceResult<List<Map<String, Object>>>();
		// 获取当前用户信息
		RestServiceResult<SysUser> user = xypjRemoteBaseService.getCurrentUser(token);
		SysUser userInfo = null;
		if (RestServiceResult.CODE_SUCCESS == user.getCode()) {
			userInfo = user.getData();
			if (null == userInfo || userInfo.getId() <= 0) {
				result.setCode(RestServiceResult.CODE_UNKNOW);
				result.setMsg("系统异常，请稍后再试！");
				ExceptionLogger.error("CreditServerController#getAchievements", "未获取到当前用户信息！");
				return result;
			}
		} else {
			result.setCode(user.getCode());
			result.setMsg(user.getMsg());
			return result;
		}

		String[] configColumns = (String[]) MapCache.get(userInfo.getId() + "_" + FRONT_TABLE_ID + ywId);
		String[] additionalColumns = { "id", "deptId", "servicecode", "checkcode" };
		List<String> showColumns = (configColumns == null || configColumns.length == 0)
				? CreditServerEnum.getDefaultShowColumnEN()
				: Arrays.asList(configColumns);

		RestServiceResult<List<Map<String, Object>>> r = new RestServiceResult<List<Map<String, Object>>>();

		List<Map<String, Object>> list = creditServerService.getAchievements(ywId,ztType, deptId, showColumns,
				additionalColumns, condition, page);

		try {
			r.setData(list);
			r.setCount(page.getTotalCount());
			r.setCode(RestServiceResult.CODE_SUCCESS);

			return r;

		} catch (Exception e) {
			r.setCode(RestServiceResult.CODE_UNKNOW);
			r.setMsg("系统异常！请联系管理员！");
		}
		return r;
	}

	@RequestMapping(value = "/getOptionalColums.json")
	public RestServiceResult<List<OptionalColumn>> getOptionalColums() {
		RestServiceResult<List<OptionalColumn>> r = new RestServiceResult<List<OptionalColumn>>();
		try {
			r = new RestServiceResult<List<OptionalColumn>>(CreditServerEnum.getOptionalColumns());
		} catch (Exception e) {
			r.setCode(RestServiceResult.CODE_UNKNOW);
			r.setMsg("系统异常");
		}
		return r;
	}

	@RequestMapping(value = "/getAdvancedConditionColumns.json")
	public RestServiceResult<List<OptionalColumn>> getAdvancedConditionColumns() {
		return new RestServiceResult<List<OptionalColumn>>(CreditServerEnum.getAdvancedConditionColumns());

	}

	@RequestMapping(value = "/addShowColunms.json")
	public RestServiceResult<String> addShowColunms(@RequestParam(value = "ywId", defaultValue = "", required = false) String ywId,@RequestParam(value = "columns", required = true) String[] columns,
			HttpServletRequest request) {
		RestServiceResult<String> result = new RestServiceResult<String>();
		// 获取当前用户信息
		RestServiceResult<SysUser> user = xypjRemoteBaseService.getCurrentUser(HttpServletHelper.getUserToken(request));
		SysUser userInfo = null;
		if (RestServiceResult.CODE_SUCCESS == user.getCode()) {
			userInfo = user.getData();
			if (null == userInfo || userInfo.getId() <= 0) {
				result.setCode(RestServiceResult.CODE_UNKNOW);
				result.setMsg("系统异常，请稍后再试！");
				ExceptionLogger.error("SzjgController#addShowColunms", "未获取到当前用户信息！");
				return result;
			}

			String column = StringUtils.join(columns, ",");
			MapCache.set(userInfo.getId() + "_" + FRONT_TABLE_ID + ywId, StringUtils.split(column, ","));
		} else {
			result.setCode(user.getCode());
			result.setMsg(user.getMsg());
			return result;
		}
		return result;
	}

	@RequestMapping(value = "/export.json")
	public RestServiceResult<Object> export(
			@RequestParam(value = "deptId", defaultValue = "", required = false) Long deptId,
			@RequestParam(value = "ztType", defaultValue = "", required = false) String ztType,
			@RequestParam(value = "ywId", defaultValue = "", required = false) String ywId,
			@RequestParam(value = "columnEN", defaultValue = "", required = false) String columnEN,
			@RequestParam(value = "condition", defaultValue = "", required = false) String condition, Page page,
			HttpServletResponse response, HttpServletRequest request) {
		String token = HttpServletHelper.getUserToken(request);
		RestServiceResult<Object> result = new RestServiceResult<Object>();
		// 获取当前用户信息
		RestServiceResult<SysUser> user = xypjRemoteBaseService.getCurrentUser(token);
		SysUser userInfo = null;
		if (RestServiceResult.CODE_SUCCESS == user.getCode()) {
			userInfo = user.getData();
			if (null == userInfo || userInfo.getId() <= 0) {
				result.setCode(RestServiceResult.CODE_UNKNOW);
				result.setMsg("系统异常，请稍后再试！");
				ExceptionLogger.error("BatchLogController#exportBookcontentList", "未获取到当前用户信息！");
				return result;
			}
		} else {
			result.setCode(user.getCode());
			result.setMsg(user.getMsg());
			return result;
		}
		// 导出结果限制1000条
		page.setLimit(1000);
		List<String> showColumns = JSONArray.parseArray(columnEN, String.class);

		List<Map<String, Object>> tableList;
		try {
			tableList = creditServerService.getAchievements(ywId , ztType, deptId, showColumns, null, condition, page);

			ExcelUtil.downloadExcel(response, tableList, "信用成果列表", "信用成果列表", showColumns,
					CreditServerEnum.getOptionalColumns());
			return result;
		} catch (Exception e) {
			result.setCode(RestServiceResult.CODE_UNKNOW);
			ExceptionLogger.error("信用成果列表导出异常！", e.getMessage(), e);

			return result;
		}
	}

	/**
	 * 成果导入
	 * @param businessType
	 * @param excelFile
	 * @param request
	 * @return
	 */
	@Transactional(rollbackFor = Exception.class)
	@RequestMapping(value = "/importExcelFile.json")
    public RestServiceResult<Object> importExcelFile(
    		@RequestParam(value = "businessType", defaultValue = "", required = false) String businessType,
    		@RequestParam("excelFile") MultipartFile excelFile,
    		HttpServletRequest request){
    	
    	RestServiceResult<SysUser> user = xypjRemoteBaseService.getCurrentUser(HttpServletHelper.getUserToken(request));
        SysUser userInfo = null;

        if (RestServiceResult.CODE_SUCCESS == user.getCode()) {
            userInfo = user.getData();
            if (null != userInfo) {

            } else {
                ExceptionLogger.error("CreditServerController#importExcelFile", "未获取到当前用户信息！");
                return new RestServiceResult<Object>(RestServiceResult.CODE_UNKNOW, "未获取到当前用户信息！");
            }
        } else {
            ExceptionLogger.error("CreditServerController#importExcelFile", "未获取到当前用户信息！");
            return new RestServiceResult<Object>(RestServiceResult.CODE_UNKNOW, "未获取到当前用户信息！");
        }
        
        RestServiceResult<Object> result = new RestServiceResult<Object>();
        
        if(null == excelFile) {
        	ExceptionLogger.error("CreditServerController#importExcelFile", "文件不齐全！");
        	result.setCode(RestServiceResult.CODE_UNKNOW);
        	result.setMsg("请上传文件！！");
        	
        	return result;
        }
        
        if (excelFile.getOriginalFilename().contains(".")) {
            String fileType = excelFile.getOriginalFilename().substring(excelFile.getOriginalFilename().lastIndexOf("."),excelFile.getOriginalFilename().length());
            if (!".xls".equalsIgnoreCase(fileType) && !".xlsx".equalsIgnoreCase(fileType) && !".XLS".equalsIgnoreCase(fileType) && !".XLSX".equalsIgnoreCase(fileType)) {
                
                result.setCode(RestServiceResult.CODE_UNKNOW);
            	result.setMsg("Excel文件暂时不支持xls/xlsx以外的Excel的格式。");
            	
            	return result;
            }
        }
        
        // 导入总数
        int totalNum = 0;
        
        BufferedWriter bw = null;
        
        CallAchievementsLog cal = new CallAchievementsLog();
        
        String batchId = UuidUtil.getInstance().getUUid();
        
        File excel = null;
		////////////////////////////////////文件上传至服务器////////////////////////////////////////////////////////
		// 导入的文件夹路径
		String dirPath = absolutepath + File.separator + "upload" + File.separator + userInfo.getOfficeId() + File.separator + "xyj_achievements" + File.separator + (new SimpleDateFormat ("yyyy-MM-dd-hh-mm-ss-SSS").format(new Date()));
		
		File dirFile = new File(dirPath);
		
		if (!dirFile.exists()) {
			dirFile.mkdirs();
		}
		
		try {
			bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(dirFile+File.separator +"log.log")));
		} catch (FileNotFoundException e2) {
			e2.printStackTrace();
		}
		
		cal.setLogPath(dirFile+File.separator +"log.log");
		
		excel = new File(dirFile + File.separator +excelFile.getOriginalFilename());
		
		// 导入excel文件
		try {
			excelFile.transferTo(excel);
		} catch (Exception e) {
			result.setCode(RestServiceResult.CODE_UNKNOW);
        	result.setMsg("Excel上传失败！请稍后再试。");
        	
        	return result;
		} 
			
        List<CallInterfaceInfo> pbrList = new ArrayList<>();
        try {
            //////////////////////////////////////////////// 解析excel//////////////////////////////////////////////////////////////
            // 针对不同类型的excel生成workbook工作簿
            Workbook wb = null;
            InputStream inp = new FileInputStream(excel);
            
            if (!inp.markSupported()) {
                inp = new PushbackInputStream(inp, 8);
            }
            if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            	wb = new HSSFWorkbook(inp);
            }else if (POIXMLDocument.hasOOXMLHeader(inp)) {
    			wb = new XSSFWorkbook(OPCPackage.open(inp));
            }else {
            	result.setCode(RestServiceResult.CODE_UNKNOW);
            	result.setMsg("解析不了的Excel！");
            	
            	return result;
            }
            
            Sheet sheet = wb.getSheetAt(0);
            
            //获得总行数
            int rowNum = sheet.getLastRowNum() + 1;
            
            lable : for (int f = 0 ; f < rowNum ; f++) {
	          	
	          	boolean canInsert = true;
	          	
	          	Row row = sheet.getRow(f);
	          	CallInterfaceInfo cfi = new CallInterfaceInfo();
	          	
	          	if(isRowEmpty(row)){
	          		continue lable;
	          	}else {
	          		totalNum++;
	          	}
	          	
	          	inLable : for (int j = 0 ; j < 8 ; j++) {
	          		Cell cell = row.getCell(j);
	          		
	          		String value = getCellValue(cell);
	          		
	          		if(f == 0) {
	          			if(!isOkTemplete(j,value)) {
	          				result.setCode(RestServiceResult.CODE_UNKNOW);
	          	        	result.setMsg("Excel并非模板，请更换模板之后再导入！");
	          	        	
	          	        	return result;
	          			}
	          			continue inLable;
	          		}
	          		
	          		if(valueIsNull(value)) {
	          			bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，为空。");
	          			bw.newLine();
            			bw.flush();
	          			canInsert = false;
	          		}else {
	          			if(value.length() > 100) {
	          				bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，长度不能大于100。");
		          			bw.newLine();
	            			bw.flush();
		          			canInsert = false;
	          			}
	          		}
	          		
	          		// 校验部门是否存在
	          		if(j == 0) {
	          			try {
	          				Long deptId = xypjRemoteBaseService.getDeptIdByName(value,HttpServletHelper.getUserToken(request));
	          				if(null == deptId || deptId == 0L) {
	          					bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，部门不存在！");
			          			bw.newLine();
		            			bw.flush();
			          			canInsert = false;
	          				}else {
	          					cfi.setDeptId(deptId);
	          				}
	          			}catch (Exception e) {
	          				e.printStackTrace();
						}
	          		}
	          		
	          		// 主体类型校验
	          		if(j == 1) {
	          			try {
	          				Integer.parseInt(value);
	          				if(ztTypeList.contains(value)) {
	          					cfi.setZtType(value);
	          				}else {
	          					bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，不在主体类型范围内。");
	          					bw.newLine();
	                			bw.flush();
	          					canInsert = false;
	          				}
	          			}catch (Exception e) {
	          				bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，不为主体类型代号。");
	          				bw.newLine();
                			bw.flush();
	          				canInsert = false;
						}
	          		}
	          		if(j == 3) {
	          			if(!valueIsNull(value)) {
	          				if(value.length() != 18) {
	          					bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，不为18位。");
	          					bw.newLine();
	                			bw.flush();
		          				canInsert = false;
	          				}
	          			}
	          		}
	          		
	          		if(j == 5) {
	          			if(!valueIsNull(value)) {
	          				try {
	          					new BigDecimal(value);
	          				}catch (Exception e) {
	          					bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，不为合理的分数。");
	          					bw.newLine();
	                			bw.flush();
			          			canInsert = false;
							}
	          			}
	          		}
	          		
	          		if(j == 7) {
	          			if(!valueIsNull(value)) {
          					for(int m = 0 ; m < timeList.size() ; m++) {
          						String strDate = timeList.get(m);
          						System.out.println(strDate);
          						System.out.println(value);
          						SimpleDateFormat format = new SimpleDateFormat(strDate);
          						format.setLenient(false);
          						try {
          							Date date = format.parse(value);
          							SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
              						format.setLenient(false);
              						String newTime = form.format(date);
          							cfi.setCallbackTime(newTime);
          							continue inLable;
          						}catch (Exception e) {
    	          					continue;
    							}
	          				}
          					bw.write("第" + (f + 1) + "行， 第" + (j + 1) + "列，时间格式不符合要求。");
          					bw.newLine();
                			bw.flush();
		          			canInsert = false;
	          			}
	          		}
	          		
	          		
	          		switch (j) {
					case 0:
						cfi.setDeptName(value);
						break;
					case 1:
								
						break;
					case 2:
						cfi.setZtName(value);
						cfi.setReturnName(value);
						break;
					case 3:
						cfi.setZtDm(value);
						cfi.setReturnUscc(value);
						break;
					case 4:
						cfi.setCreditlevel(value);
						break;
					case 5:
						cfi.setReturnScore(value);
						break;
					case 6:
						cfi.setCallbackUsage(value);
						break;
					case 7:
						
						break;
//					case 8:
//						cfi.setCallbackTime(value);
//						break;

					default:
						break;
					}
	          		cfi.setBatchId(batchId);
	          		cfi.setServicecode(businessType);
	          	}	
	          	// 可以插入的行才放入list
	          	if(canInsert) {
	          		if(f != 0) {
	          			pbrList.add(cfi);
	          		}
	          	}
            }		
        }catch (Exception e) {
        	result.setCode(RestServiceResult.CODE_UNKNOW);
        	result.setMsg("Excel读取文件异常！请检查文件内容稍后再试！");
        	
        	return result;
		}			
        
        cal.setTotalNum(totalNum - 1);
        cal.setBatchId(batchId);
        
        Map<String , Object> map = new HashMap<>();
        map.put("totalNum", totalNum - 1);
        if(cal.getTotalNum() != cal.getSuccessNum()) {
        	map.put("logPath", cal.getLogPath());
        }
        
        //成功数量
        int successNum = 0 ; 
        try {
        	if(pbrList.size() > 0) {
        		successNum = creditServerService.insertCallInfos(pbrList);
        	}
        	
        	map.put("successNum", successNum);
        	
        	if(successNum > 0) {
        		cal.setSuccessNum(successNum);
        		creditServerService.insertCallLog(cal);   // 插入日志
        	}
        	
        }catch (Exception e) {
        	result.setCode(RestServiceResult.CODE_UNKNOW);
        	result.setMsg("Excel入库异常，请稍后再试！");
        	
        	return result;
		}
        
        result.setCode(RestServiceResult.CODE_SUCCESS);
        result.setData(map);
        result.setMsg("导入成功！");
        
        try {
			bw.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
        
        return result;
    }

	/**
	 * 模板下载
	 * 
	 * @param response
	 * @param request
	 * @throws IOException
	 */
	@RequestMapping(value = "/templeteDownload.json")
	public void templeteDownload(HttpServletResponse response, HttpServletRequest request) throws IOException {

		FileUtils.downloadFile(CreditServerController.class.getClassLoader().getResource("mb/调用结果导入模板.xlsx").getPath(),
				response);
	}

	/**
	 * 日志下载
	 * 
	 * @param logPath
	 *            日志路径
	 * @param request
	 * @return
	 */
	@RequestMapping(value = "/downloadLog.json")
	public void downloadLog(@RequestParam(value = "logPath", required = true, defaultValue = "") String logPath,
			HttpServletRequest request, HttpServletResponse response) {

		if (!new File(logPath).exists()) {
			throw new ServiceException("文件不存在，或者已经被移除！");
		}

		FileUtils.downloadFile(logPath, response);

	}

	// 获取单元格内容
	private String getCellValue(Cell cell) {
		
		Date date = null;
		String value = "";
		
		Pattern pattern1 = Pattern.compile("[0-9]{4}/[0-9]{1}/[0-9]{1}");
        Pattern pattern2 = Pattern.compile("[0-9]{4}/[0-9]{1}/[0-9]{2}");
        Pattern pattern3 = Pattern.compile("[0-9]{4}/[0-9]{2}/[0-9]{1}");
        Pattern pattern4 = Pattern.compile("[0-9]{4}/[0-9]{2}/[0-9]{2}");
		
		if (cell == null)
			return "";
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue().trim();
//			try {
//                if (pattern1.matcher(value).matches() || pattern2.matcher(value).matches()|| pattern3.matcher(value).matches() || pattern4.matcher(value).matches()) {
//                    value = DateUtil.getInstance().parseDateStr(value, DateUtil.YYYY__MM__DD);
//                }
//            } catch (Exception e) {
//               System.out.println("日期类型无法转换" + value);
//            }
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return cell.getCellFormula();
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//			if (HSSFDateUtil.isCellDateFormatted(cell)) {
//                date = cell.getDateCellValue();
//                if (date != null) {
//                    value = DateUtil.getInstance().dateToStr(date, DateUtil.YYYY__MM__DD);
//                } else {
//                    value = "";
//                }
//            }
//			short format = cell.getCellStyle().getDataFormat();
//			System.out.println("format:" + format + ";;;;;value:" + cell.getNumericCellValue());
//			SimpleDateFormat sdf = null;
//			if (format == 14 || format == 31 || format == 57 || format == 58 || (176 <= format && format <= 178)
//					|| (182 <= format && format <= 196) || (210 <= format && format <= 213) || (208 == format)) { // 日期
//				sdf = new SimpleDateFormat("yyyy-MM-dd");
//			} else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间
//				sdf = new SimpleDateFormat("HH:mm");
//			} else { // 不是日期格式
				return new DecimalFormat("#.######").format(cell.getNumericCellValue());
//			}
//			double value = cell.getNumericCellValue();
//			Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
//			if (date == null || "".equals(date)) {
//				return "";
//			}
//			String result = "";
//			try {
//				result = sdf.format(date);
//			} catch (Exception e) {
//				e.printStackTrace();
//				return "";
//			}
//			return result;
		}
		return value;
	}

	private static boolean isRowEmpty(Row row) {
		if (null == row) {
			return true;
		}

		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
			Cell cell = row.getCell(c);
			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
				return false;
			}
		}
		return true;
	}

	private boolean isOkTemplete(int index, String value) {

		if (excelTemplete.get(index).equals(value)) {
			return true;
		}

		return false;
	}

	private boolean valueIsNull(String value) {
		if (StringUtils.isEmpty(value)) {
			return true;
		}
		return false;
	}

}
